package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import objects.OBUentry;

public class OBUdata {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public OBUdata() throws Exception {
	
		try {
			// This will load the MySQL driver, each DB has its own driver
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException X) {
				System.out.println("Driver could not be loadded!!!");
			}
			// Setup the connection with the DB
			String pw = "c$411r3d$p10";
			connect = DriverManager.getConnection("jdbc:mysql://raphael.cs.odu.edu/411redsp10obu?" + "user=411redsp10&password="+pw);

		} catch (Exception e) {
			throw e;
		}
	}

	public void getOBUEntry( OBUentry obuentry1) throws SQLException {
        // Statements allow to issue SQL queries to the database
		String stmt1 = "SELECT * FROM 411redsp10obu.obudata WHERE id = ?";
        preparedStatement = connect.prepareStatement(stmt1);
		preparedStatement.setInt(1,obuentry1.getID());
        resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
            obuentry1.setLat(resultSet.getDouble("latitude"));
            obuentry1.setLong(resultSet.getDouble("longitude"));
            obuentry1.setSpeed(resultSet.getInt("speed"));
            obuentry1.setSeatBelt(resultSet.getBoolean("seatbelt"));
		}
	}

    public void addOBUentry(OBUentry obuentry2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into 411redsp10obu.obudata(latitude,longitude," +
        		"speed,seatbelt) values (?, ?, ?, ?)");
        preparedStatement.setDouble(1, obuentry2.getLat());
        preparedStatement.setDouble(2, obuentry2.getLong());
        preparedStatement.setInt(3, obuentry2.getSpeed());
        preparedStatement.setBoolean(4, obuentry2.getSeatBelt());
        preparedStatement.executeUpdate();
    }

	public Integer getSize() throws SQLException{
		Integer mySize=0;
		statement = connect.createStatement();
		resultSet = statement.executeQuery("select count(*) size from 411redsp10obu.obudata");
		
		while (resultSet.next()) {
			mySize = resultSet.getInt("size");
		}
		
		return mySize;
	}

	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {}
	}

}
